package com.jiyuren.spring.utils;

import com.jiyuren.spring.bean.WebSite;

import org.apache.commons.dbcp.BasicDataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2017/3/23.
 */
public class DatabaseSqliteUtil {
    /**
     * 数据源
     */
    private BasicDataSource dataSource;
    /**
     * 数据库连接
     */
    public Connection conn;

    /**
     * 获取数据源
     *
     * @return 数据源
     */
    public BasicDataSource getDataSource() {
        return dataSource;
    }

    /**
     * 设置数据源
     *
     * @param dataSource 数据源
     */
    public void setDataSource(BasicDataSource dataSource) {
        this.dataSource = dataSource;
    }

    /**
     * 获取数据库连接
     *
     * @return conn
     */
    public Connection getConnection() {
        try {
            conn = dataSource.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return conn;
    }

    public void initTable(Connection conn) {
        Statement stat = null;
        try {
            stat = conn.createStatement();
            stat.execute("create table if not exists website " +
                    "(id integer primary key AUTOINCREMENT, url varchar(1000),title_name varchar(255),"
                    + "access_time_interval integer(10),time_out integer(10),"
                    + "access_time varchar(255),is_del integer(1),"
                    + "is_run integer(1),access_times integer(10))");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    /**
     * 关闭数据库连接
     *
     * @param conn
     */
    public static void closeConnection(Connection conn) {
        if (null != conn) {
            try {
                conn.close();
                conn = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取执行SQL的工具
     *
     * @param conn 数据库连接
     * @return stmt
     */
    public static int getFoundRows(Connection conn) {
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = getStatement(conn);
            rs = stmt.executeQuery("SELECT FOUND_ROWS()");
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeStatement(stmt);
            closeResultSet(rs);
        }
        return 0;
    }

    /**
     * 获取执行SQL的工具
     *
     * @param conn 数据库连接
     * @return stmt
     */
    public static Statement getStatement(Connection conn) {
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return stmt;
    }

    /**
     * 获取执行SQL的工具
     *
     * @param conn 数据库连接
     * @param sql  SQL语句
     * @return prepStmt
     */
    public static PreparedStatement getPrepStatement(Connection conn, String sql) {
        PreparedStatement prepStmt = null;
        try {
            prepStmt = conn.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return prepStmt;
    }

    /**
     * 关闭数据库资源
     *
     * @param stmt
     */
    public static void closeStatement(Statement stmt) {
        if (null != stmt) {
            try {
                stmt.close();
                stmt = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭数据库资源
     *
     * @param prepStmt
     */
    public static void closePrepStatement(PreparedStatement prepStmt) {
        if (null != prepStmt) {
            try {
                prepStmt.close();
                prepStmt = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取结果集
     *
     * @param stmt 执行SQL的工具
     * @param sql  SQL语句
     * @return 结果集
     */
    public static ResultSet getResultSet(Statement stmt, String sql) {
        ResultSet rs = null;
        try {
            rs = stmt.executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    /**
     * 关闭数据库资源
     *
     * @param rs
     */
    public static void closeResultSet(ResultSet rs) {
        if (null != rs) {
            try {
                rs.close();
                rs = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static Boolean setAutoCommit(Connection conn, boolean commitStatus) {
        if (conn == null) {
            return true;
        }
        try {
            boolean commit = conn.getAutoCommit();
            conn.setAutoCommit(commitStatus);
            return commit;
        } catch (SQLException e1) {
            e1.printStackTrace();
            return true;
        }
    }

    public static boolean rollback(Connection conn, boolean oldCommitStatus) {
        if (conn == null) {
            return true;
        }
        try {
            conn.rollback(); // 事物回滚
            conn.setAutoCommit(oldCommitStatus);
            return true;
        } catch (SQLException e1) {
            e1.printStackTrace();
            return false;
        }
    }

    public static boolean commit(Connection conn, boolean oldCommitStatus) {
        if (conn == null) {
            return true;
        }
        try {
            conn.commit(); // 事物回滚
            conn.setAutoCommit(oldCommitStatus);
            return true;
        } catch (SQLException e1) {
            e1.printStackTrace();
            return false;
        }
    }

    public static int getLastId(PreparedStatement ps) {
        ResultSet rs = null;
        try {
            rs = ps.getGeneratedKeys();
            if (rs != null && rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
// TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            closeResultSet(rs);
        }
        return -1;
    }

    public List<WebSite> getList(Connection conn) {
        List<WebSite> list = new ArrayList<WebSite>();
        int pos = 0;
        PreparedStatement stat = null;
        ResultSet rs = null;
        try {
            String sql = "select * from website where is_del = ?";
            stat = conn.prepareStatement(sql);
            stat.setInt(1,0);
            rs = stat.executeQuery();
            WebSite site = null;
            while (rs.next()) {
                site = new WebSite();
                String url = rs.getString("url");
                String titleName = rs.getString("title_name");
                int accessTimeInterval = rs.getInt("access_time_interval");
                String accessTime = rs.getString("access_time");
                int accessTimes = rs.getInt("access_times");
                int isDel = rs.getInt("is_del");
                int isRun = rs.getInt("is_run");
                int timeOut = rs.getInt("time_out");
                int id = rs.getInt("id");
                site.setId(id);
                site.setTime_out(timeOut);
                site.setIs_run(isRun);
                site.setIs_del(isDel);
                site.setAccess_times(accessTimes);
                site.setAccess_time(accessTime);
                site.setAccess_time_interval(accessTimeInterval);
                site.setTitle_name(titleName);
                site.setUrl(url);
                site.setIndex(pos);
                site.setIs_green(isRun);
                list.add(site);
                pos += 1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResultSet(rs);
            closePrepStatement(stat);
            closeConnection(conn);
        }
        return list;
    }
}
